package com.migration.generator;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库模型生成器
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class ModelGenerator {

    @Autowired
    @Qualifier("oldDataSource")
    private DataSource oldDataSource;

    @Autowired
    @Qualifier("newDataSource")
    private DataSource newDataSource;

    private final Configuration freemarkerConfig;

    /**
     * 初始化Freemarker配置
     */
    public ModelGenerator() {
        freemarkerConfig = new Configuration(Configuration.VERSION_2_3_32);
        try {
            File templateDir = new File("src/main/resources/templates");
            if (!templateDir.exists()) {
                templateDir.mkdirs();
            }
            freemarkerConfig.setDirectoryForTemplateLoading(templateDir);
        } catch (IOException e) {
            log.error("初始化Freemarker配置失败", e);
        }
    }

    /**
     * 将蛇形命名转换为驼峰命名
     * @param snakeStr 蛇形命名字符串
     * @return 驼峰命名字符串
     */
    private String toCamelCase(String snakeStr) {
        if (snakeStr == null || snakeStr.isEmpty()) {
            return snakeStr;
        }

        StringBuilder result = new StringBuilder();
        boolean nextUpper = false;

        for (char c : snakeStr.toCharArray()) {
            if (c == '_') {
                nextUpper = true;
            } else {
                if (nextUpper) {
                    result.append(Character.toUpperCase(c));
                    nextUpper = false;
                } else {
                    result.append(c);
                }
            }
        }

        return result.toString();
    }

    /**
     * 将蛇形命名转换为大驼峰命名（首字母大写）
     * @param snakeStr 蛇形命名字符串
     * @return 大驼峰命名字符串
     */
    private String toPascalCase(String snakeStr) {
        String camelCase = toCamelCase(snakeStr);
        if (camelCase == null || camelCase.isEmpty()) {
            return camelCase;
        }
        return Character.toUpperCase(camelCase.charAt(0)) + camelCase.substring(1);
    }

    /**
     * 获取MySQL数据类型到Java类型的映射
     * @param sqlType SQL类型名称
     * @param columnSize 列大小
     * @param decimalDigits 小数位数
     * @return Java类型名称
     */
    private String getJavaType(String sqlType, int columnSize, int decimalDigits) {
        if (sqlType == null) {
            return "String";
        }

        switch (sqlType.toUpperCase()) {
            case "VARCHAR":
            case "CHAR":
            case "TEXT":
            case "LONGTEXT":
            case "MEDIUMTEXT":
            case "ENUM":
            case "SET":
                return "String";
            case "INT":
            case "INTEGER":
            case "MEDIUMINT":
            case "YEAR":
            case "INT UNSIGNED":
                return "Integer";
            case "BIGINT":
            case "BIGINT UNSIGNED":
                return "Long";
            case "TINYINT":
                return columnSize == 1 ? "Boolean" : "Byte";
            case "SMALLINT":
                return "Short";
            case "FLOAT":
                return "Float";
            case "DOUBLE":
            case "DOUBLE UNSIGNED":
                return "Double";
            case "DECIMAL":
            case "NUMERIC":
                return "BigDecimal";
            case "DATE":
                return "java.time.LocalDate";
            case "TIME":
                return "java.time.LocalTime";
            case "DATETIME":
            case "TIMESTAMP":
                return "java.time.LocalDateTime";
            case "BLOB":
            case "LONGBLOB":
            case "MEDIUMBLOB":
            case "BINARY":
            case "VARBINARY":
                return "byte[]";
            case "BIT":
                return columnSize > 1 ? "byte[]" : "Boolean";
            default:
                log.warn("未知列类型: {}, 默认映射为String", sqlType);
                return "String";
        }
    }

    /**
     * 列信息
     */
    @Data
    public static class ColumnInfo {
        private String name;            // 列名
        private String javaName;        // Java属性名
        private String javaType;        // Java类型
        private String sqlType;         // SQL类型
        private boolean primaryKey;     // 是否主键
        private boolean nullable;       // 是否可为空
        private String defaultValue;    // 默认值
        private String comment;         // 注释
        private int length;             // 长度
        private int precision;          // 精度
        private int scale;              // 小数位数
    }

    /**
     * 表信息
     */
    @Data
    public static class TableInfo {
        private String name;                 // 表名
        private String className;            // 类名
        private String comment;              // 表注释
        private List<ColumnInfo> columns;    // 列信息列表
    }

    /**
     * 从数据源获取所有表信息
     * @param dataSource 数据源
     * @param tablePrefix 表前缀
     * @param excludePrefixes 排除的表前缀列表
     * @return 表信息列表
     */
    public List<TableInfo> getTablesFromDataSource(DataSource dataSource, String tablePrefix, List<String> excludePrefixes) {
        List<TableInfo> tables = new ArrayList<>();

        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            String catalog = conn.getCatalog();
            String schema = conn.getSchema();

            // 获取所有表
            try (ResultSet rs = metaData.getTables(catalog, schema, "%", new String[]{"TABLE"})) {
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");

                    // 排除指定前缀的表
                    if (excludePrefixes != null && !excludePrefixes.isEmpty()) {
                        boolean exclude = false;
                        for (String prefix : excludePrefixes) {
                            if (tableName.startsWith(prefix)) {
                                exclude = true;
                                break;
                            }
                        }
                        if (exclude) {
                            continue;
                        }
                    }

                    TableInfo table = new TableInfo();
                    table.setName(tableName);

                    // 从表名生成类名，移除前缀
                    String classNameBase = tableName;
                    if (tablePrefix != null && !tablePrefix.isEmpty() && tableName.startsWith(tablePrefix)) {
                        classNameBase = tableName.substring(tablePrefix.length());
                    }
                    table.setClassName(toPascalCase(classNameBase));

                    // 获取表注释
                    String tableComment = rs.getString("REMARKS");
                    table.setComment(tableComment != null ? tableComment : "");

                    // 获取表的列信息
                    table.setColumns(getColumnsFromTable(metaData, catalog, schema, tableName));

                    tables.add(table);
                }
            }
        } catch (SQLException e) {
            log.error("获取表信息失败", e);
        }

        return tables;
    }

    /**
     * 获取表的所有列信息
     * @param metaData 数据库元数据
     * @param catalog 目录
     * @param schema 模式
     * @param tableName 表名
     * @return 列信息列表
     */
    private List<ColumnInfo> getColumnsFromTable(DatabaseMetaData metaData, String catalog, String schema, String tableName)
            throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();

        // 获取主键信息
        Set<String> primaryKeys = new HashSet<>();
        try (ResultSet pks = metaData.getPrimaryKeys(catalog, schema, tableName)) {
            while (pks.next()) {
                primaryKeys.add(pks.getString("COLUMN_NAME"));
            }
        }

        // 获取列信息
        try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, "%")) {
            while (rs.next()) {
                ColumnInfo column = new ColumnInfo();

                String columnName = rs.getString("COLUMN_NAME");
                column.setName(columnName);
                column.setJavaName(toCamelCase(columnName));

                String typeName = rs.getString("TYPE_NAME");
                column.setSqlType(typeName);

                int columnSize = rs.getInt("COLUMN_SIZE");
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                column.setLength(columnSize);
                column.setPrecision(columnSize);
                column.setScale(decimalDigits);

                column.setJavaType(getJavaType(typeName, columnSize, decimalDigits));
                column.setPrimaryKey(primaryKeys.contains(columnName));
                column.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                column.setDefaultValue(rs.getString("COLUMN_DEF"));
                column.setComment(rs.getString("REMARKS"));

                columns.add(column);
            }
        }

        return columns;
    }

    /**
     * 生成实体类文件
     * @param tables 表信息列表
     * @param packageName 包名
     * @param outputDir 输出目录
     * @param templateName 模板名
     */
    public void generateEntityFiles(List<TableInfo> tables, String packageName, String outputDir, String templateName) {
        // 确保输出目录存在
        File dir = new File(outputDir);
        if (!dir.exists()) {
            dir.mkdirs();
        }

        try {
            // 加载模板
            Template template = freemarkerConfig.getTemplate(templateName);

            // 为每个表生成实体类
            for (TableInfo table : tables) {
                String className = table.getClassName();
                File outputFile = new File(dir, className + ".java");

                try (Writer writer = new FileWriter(outputFile)) {
                    Map<String, Object> data = new HashMap<>();
                    data.put("packageName", packageName);
                    data.put("table", table);

                    // 处理需要导入的类
                    Set<String> imports = new HashSet<>();
                    for (ColumnInfo column : table.getColumns()) {
                        String type = column.getJavaType();
                        if (type.contains(".")) {
                            imports.add(type);
                        } else if (type.equals("BigDecimal")) {
                            imports.add("java.math.BigDecimal");
                        }
                    }
                    data.put("imports", imports);

                    // 应用模板生成Java代码
                    template.process(data, writer);
                    log.info("已生成实体类: {}", outputFile.getAbsolutePath());
                }
            }
        } catch (IOException | TemplateException e) {
            log.error("生成实体类文件失败", e);
        }
    }

    /**
     * 清空目录中的所有文件
     * @param dirPath 目录路径
     */
    public void cleanDirectory(String dirPath) {
        File dir = new File(dirPath);
        if (dir.exists() && dir.isDirectory()) {
            File[] files = dir.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isFile()) {
                        if (file.delete()) {
                            log.info("已删除文件: {}", file.getAbsolutePath());
                        } else {
                            log.warn("无法删除文件: {}", file.getAbsolutePath());
                        }
                    }
                }
            }
        }
    }

    /**
     * 生成指定数据源的所有实体类
     * @param dataSource 数据源
     * @param packageName 包名
     * @param outputDir 输出目录
     * @param tablePrefix 表前缀
     * @param excludePrefixes
     */
    public void generateEntitiesFromDataSource(
            DataSource dataSource,
            String packageName,
            String outputDir,
            String tablePrefix,
            List<String> excludePrefixes
    ) {
        // 先清空目录
        cleanDirectory(outputDir);
        // 再生成新的实体类
        List<TableInfo> tables = getTablesFromDataSource(dataSource, tablePrefix, excludePrefixes);
        generateEntityFiles(tables, packageName, outputDir, "entity.ftl");
    }

    /**
     * 生成旧数据库的所有实体类
     * @param tablePrefix 表前缀
     * @param excludePrefixes 排除的表前缀列表
     */
    public void generateOldEntities(String tablePrefix, List<String> excludePrefixes) {
        String packageName = "com.migration.model.olddb";
        String outputDir = "src/main/java/com/migration/model/olddb";
        generateEntitiesFromDataSource(oldDataSource, packageName, outputDir, tablePrefix, excludePrefixes);
    }

    /**
     * 生成新数据库的所有实体类
     * @param tablePrefix 表前缀
     * @param excludePrefixes 排除的表前缀列表
     */
    public void generateNewEntities(String tablePrefix, List<String> excludePrefixes) {
        String packageName = "com.migration.model.newdb";
        String outputDir = "src/main/java/com/migration/model/newdb";
        generateEntitiesFromDataSource(newDataSource, packageName, outputDir, tablePrefix, excludePrefixes);
    }

    /**
     * 获取旧数据库数据源
     * @return 旧数据库数据源
     */
    public DataSource getOldDataSource() {
        return oldDataSource;
    }

    /**
     * 获取新数据库数据源
     * @return 新数据库数据源
     */
    public DataSource getNewDataSource() {
        return newDataSource;
    }

    /**
     * 生成旧数据库模型
     *
     * @param config 配置参数，包含tablePrefix和excludePrefixes
     * @return 操作结果
     */
    public Map<String, Object> generateOldDbModels(Map<String, Object> config) {
        Map<String, Object> result = new HashMap<>();

        try {
            String tablePrefix = (String) config.getOrDefault("tablePrefix", "");
            List<String> excludePrefixes = (List<String>) config.getOrDefault("excludePrefixes", List.of());

            List<TableInfo> tables = getTablesFromDataSource(oldDataSource, tablePrefix, excludePrefixes);
            int count = tables.size();
            generateOldEntities(tablePrefix, excludePrefixes);
            log.info("旧数据库模型生成成功，共生成{}个实体类", count);

            result.put("success", true);
            result.put("message", "旧数据库模型生成成功");
            result.put("count", count);
            result.put("tables", tables.stream().map(TableInfo::getName).collect(Collectors.toList()));

            return result;
        } catch (Exception e) {
            log.error("生成旧数据库模型失败", e);
            result.put("success", false);
            result.put("message", "旧数据库模型生成失败: " + e.getMessage());

            return result;
        }
    }

    /**
     * 生成新数据库模型
     *
     * @param config 配置参数，包含tablePrefix和excludePrefixes
     * @return 操作结果
     */
    public Map<String, Object> generateNewDbModels(Map<String, Object> config) {
        Map<String, Object> result = new HashMap<>();

        try {
            String tablePrefix = (String) config.getOrDefault("tablePrefix", "");
            List<String> excludePrefixes = (List<String>) config.getOrDefault("excludePrefixes", List.of());

            List<TableInfo> tables = getTablesFromDataSource(newDataSource, tablePrefix, excludePrefixes);
            int count = tables.size();
            generateNewEntities(tablePrefix, excludePrefixes);
            log.info("新数据库模型生成成功，共生成{}个实体类", count);

            result.put("success", true);
            result.put("message", "新数据库模型生成成功");
            result.put("count", count);
            result.put("tables", tables.stream().map(TableInfo::getName).collect(Collectors.toList()));

            return result;
        } catch (Exception e) {
            log.error("生成新数据库模型失败", e);
            result.put("success", false);
            result.put("message", "新数据库模型生成失败: " + e.getMessage());

            return result;
        }
    }
}
